-- 1. 如果数据库 hotel 不存在，则创建
IF NOT EXISTS (
    SELECT name FROM sys.databases WHERE name = N'hotel'
)
BEGIN
    CREATE DATABASE hotel;
END
GO

-- 2. 切换到 hotel 数据库
USE hotel;
GO

-- 3. 创建 Customers 表（客户信息）
IF OBJECT_ID('Customers', 'U') IS NULL
BEGIN
CREATE TABLE Customers (
                           CustomerID INT PRIMARY KEY IDENTITY(1,1),
                           Username NVARCHAR(50) NOT NULL UNIQUE,
                           Password NVARCHAR(255) NOT NULL,
                           Name NVARCHAR(100) NOT NULL,
                           IDNumber NVARCHAR(18) NOT NULL UNIQUE,
                           Phone NVARCHAR(20) NOT NULL
);
END
GO

-- 4. 创建 Admins 表（管理员）
IF OBJECT_ID('Admins', 'U') IS NULL
BEGIN
CREATE TABLE Admins (
                        AdminID INT PRIMARY KEY IDENTITY(1,1),
                        Username NVARCHAR(50) NOT NULL UNIQUE,
                        Password NVARCHAR(255) NOT NULL
);
END
GO

-- 5. 创建 Rooms 表（客房信息）
IF OBJECT_ID('Rooms', 'U') IS NULL
BEGIN
CREATE TABLE Rooms (
                       RoomID INT PRIMARY KEY IDENTITY(1,1),
                       RoomNumber NVARCHAR(10) NOT NULL UNIQUE,
                       RoomType NVARCHAR(50) NOT NULL,
                       Status NVARCHAR(20) NOT NULL ,
                       Price DECIMAL(10, 2) NOT NULL
);
END
GO

-- 6. 创建 Bookings 表（订单信息）
IF OBJECT_ID('Bookings', 'U') IS NULL
BEGIN
CREATE TABLE Bookings (
                          BookingID INT PRIMARY KEY IDENTITY(1,1),
                          CustomerID INT NOT NULL,
                          RoomID INT NOT NULL,
                          BookingTime DATETIME NOT NULL DEFAULT GETDATE(),
                          CheckInTime DATETIME NOT NULL,
                          CheckOutTime DATETIME NOT NULL,
                          TotalAmount DECIMAL(10, 2) NOT NULL,
                          Status NVARCHAR(20) NOT NULL DEFAULT '预订中'

                          FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
                          FOREIGN KEY (RoomID) REFERENCES Rooms(RoomID)
);
END
GO
